﻿using MyWEbProject.DAO.Entities;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace MyWEbProject.DAO
{
    public class CustomerDAO
    {
        SqlConnection _Conn = null;
        public CustomerDAO()
        {
            String connectionString = "server=localhost\\SQLEXPRESS;database=NursatTestDB;uid=sa;password=Vizrt_123;";
            _Conn = new SqlConnection(connectionString);
            _Conn.Open();
        }

        //public int EditCustomer(int id)
        //{
        //    if (_Conn.State != System.Data.ConnectionState.Open)
        //        _Conn.Open();

        //    String SQL = String.Empty;
        //    if (id == 0)
        //        SQL = String.Format("INSERT INTO Profession (Name) VALUES ('{0}')", profession.Name);
        //    else
        //        SQL = String.Empty;//TODO: UPDATE SQL Should be here
        //    SqlCommand command = new SqlCommand(SQL, _Conn);
        //    command.CommandType = System.Data.CommandType.Text;

        //    try
        //    {
        //        int effectedRows = command.ExecuteNonQuery();
        //        return effectedRows;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
        //            _Conn.Close();
        //    }
        //}
 

        //public List<Customer> GetAll()
        //{
        //    if (_Conn.State != System.Data.ConnectionState.Open)
        //        _Conn.Open();

        //    String SQL = "SELECT * FROM Customer";
        //    SqlCommand command = new SqlCommand(SQL, _Conn);
        //    command.CommandType = System.Data.CommandType.Text;
        //    SqlDataReader reader = null;
        //    try
        //    {
        //        reader = command.ExecuteReader();
        //        List<Customer> customers = new List<Customer>();
        //        while (reader.Read())
        //        {
        //            Customer customer = new Customer();
        //            customer.ID = Convert.ToInt32(reader["ID"]);
        //            customer.Name = Convert.ToString(reader["Name"]);
        //            customer.Address = Convert.ToString(reader["Address"]);
        //            customer.Age = Convert.ToInt32(reader["Age"]);
        //            //customer.Profession1 = (Profession)reader["Profession1"];

        //            customers.Add(customer);
        //        }
        //        reader.Close();
        //        return customers;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
        //            _Conn.Close();
        //    }
        //}

        public List<Customer> GetAllCustomer()
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();

            #region Obsolete Code
            //String SqlForProfession = "SELECT * FROM Profession";
            //SqlCommand command = new SqlCommand(SqlForProfession, _Conn);
            //command.CommandType = System.Data.CommandType.Text;
            //SqlDataReader reader = null;
            ////List<Profession> professions = new List<Profession>();
            //Dictionary<int , Profession> profs = new Dictionary<int,Profession>();
            
            //try
            //{
            //    reader = command.ExecuteReader();
                
            //    while (reader.Read())
            //    {
            //        int id  = Convert.ToInt32(reader["ID"]);
            //        string profName = Convert.ToString(reader["Name"]);
            //        Profession p = new Profession() { ID = id, Name = profName };
            //        profs.Add(id, p);
            //    }
            //    reader.Close();
            //}
            //catch { }
            #endregion

            String SQL = @"SELECT 
	                        Customer.ID
	                        , Customer.Name
	                        , Customer.Age
	                        , Customer.Address
	                        , Customer.Created_on
	                        , Customer.Profession
	                        , Profession.Name AS ProfessionName
                        FROM Customer 
	                        INNER JOIN Profession ON Profession.ID = Customer.Profession";
            SqlCommand command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;
            SqlDataReader reader = null;
            try
            {
                reader = command.ExecuteReader();
                List<Customer> customers = new List<Customer>();
                ProfessionDAO professionDAO = new ProfessionDAO();
                while (reader.Read())
                {
                    Customer customer = new Customer();
                    customer.ID = Convert.ToInt32(reader["ID"]);
                    customer.Name = Convert.ToString(reader["Name"]);
                    customer.Address = Convert.ToString(reader["Address"]);
                    customer.Age = Convert.ToInt32(reader["Age"]);
                    customer.Profession = Convert.ToInt32(reader["Profession"]); 

                    //Profession p = profs[customer.Profession]; 
                    //customer.Profession1 = professionDAO.GetByID(customer.Profession);  
                    customer.Profession1 = new Profession();
                    customer.Profession1.ID = Convert.ToInt32(reader["Profession"]);
                    customer.Profession1.Name = Convert.ToString(reader["ProfessionName"]);

                    customer.Created_on = Convert.ToDateTime(reader["Created_on"]);
                    

                    customers.Add(customer);                    
                }
                reader.Close();
                return customers;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();
            }
        }


        public Customer GetByID(int id)
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();


            String SqlForProfession = "SELECT * FROM Profession";
            SqlCommand command = new SqlCommand(SqlForProfession, _Conn);
            command.CommandType = System.Data.CommandType.Text;
            SqlDataReader reader = null;
            //List<Profession> professions = new List<Profession>();
            Dictionary<int, Profession> profs = new Dictionary<int, Profession>();
            
            try
            {
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    int p_id = Convert.ToInt32(reader["ID"]);
                    string profName = Convert.ToString(reader["Name"]);
                    Profession p = new Profession() { ID = p_id, Name = profName };
                    profs.Add(p_id, p);
                }
                reader.Close();
            }
            catch { }

            String SQL = String.Format("SELECT * FROM Customer WHERE ID = {0}", id);
            command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;
            reader = null;

            try
            {
                reader = command.ExecuteReader();
                Customer customer = new Customer();
                while (reader.Read())
                {
                    customer.ID = Convert.ToInt32(reader["ID"]);
                    customer.Name = Convert.ToString(reader["Name"]);
                    customer.Address = Convert.ToString(reader["Address"]);
                    customer.Age = Convert.ToInt32(reader["Age"]);
                    customer.Profession = Convert.ToInt32(reader["Profession"]); 

                    Profession p = profs[customer.Profession]; 
                    customer.Profession1 = p;  

                    customer.Created_on = Convert.ToDateTime(reader["Created_on"]);
                }
                reader.Close();
                return customer;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();
            }
        }

        public int SaveCustomer(Customer customer)
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();

            String SQL = String.Empty;
            if (customer.ID == 0)
                SQL = String.Format("INSERT INTO Customer (Name, Address, Age, Profession, Created_on) VALUES ('{0}','{1}','{2}','{3}','{4}')", customer.Name, customer.Address, customer.Age, customer.Profession, customer.Created_on);
            else
            {
               // SQL = String.Format("UPDATE Customer SET Name='",customer.Name,"', Address='", customer.Address,"', Age=", customer.Age,", Profession=", customer.Profession ," WHERE ID=", customer.ID);//TODO: UPDATE SQL Should be here
                SQL = "UPDATE Customer SET Name='"+customer.Name+"', Address='"+customer.Address+"', Age="+customer.Age+", Profession="+customer.Profession+" WHERE ID="+ customer.ID;
                
            }
            SqlCommand command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;

            try
            {
                int effectedRows = command.ExecuteNonQuery();
                return effectedRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();
            }
        }


        public int DeleteCustomer(int id)
        {
            if (_Conn.State != System.Data.ConnectionState.Open)
                _Conn.Open();

            String SQL = String.Empty;
            
            
            // SQL = String.Format("UPDATE Customer SET Name='",customer.Name,"', Address='", customer.Address,"', Age=", customer.Age,", Profession=", customer.Profession ," WHERE ID=", customer.ID);//TODO: UPDATE SQL Should be here
            SQL = "DELETE FROM Customer WHERE ID=" + id;

            
            SqlCommand command = new SqlCommand(SQL, _Conn);
            command.CommandType = System.Data.CommandType.Text;

            try
            {
                int effectedRows = command.ExecuteNonQuery();
                return effectedRows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == System.Data.ConnectionState.Open)
                    _Conn.Close();
            }
        }

      
    }
}